package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmscommon.utils.MyLocalDateTimeTypeHandler;
import com.sunda.spmsorder.entity.OrderWorkshop;
import com.sunda.spmsorder.entity.vo.OrderWorkshopQueryResultVo;
import com.sunda.spmsorder.entity.vo.OrderWorkshopQueryVo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 车间请购单 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-02-02
 */
@Mapper
@Component
public interface OrderWorkshopMapper extends BaseMapper<OrderWorkshop> {

    @Select("<script>" +
            "SELECT DISTINCT OW.UUID AS \"uuid\",\n" +
            "       OW.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "       OW.URGENCY AS \"urgency\",\n" +
            "       OW.APPLICATION_DATE AS \"applicationDate\",\n" +
            "       OW.DATE_REQUIRED AS \"dateRequired\",\n" +
            "       OW.SUBJECT AS \"subject\",\n" +
            "       OW.WORK_CODE AS \"userId\",\n" +
            "       OW.LAST_NAME AS \"lastName\",\n" +
            "       OW.WERKS AS \"werks\",\n" +
            "       OW.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OW.JUSTIFICATION AS \"justification\",\n" +
            "       OW.PROJECT_CODE AS \"projectCode\",\n" +
            "       OW.PROJECT_TYPE AS \"projectType\",\n" +
            "       OW.PROJECT_STATUS AS \"projectStatus\",\n" +
            "       OW.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "       OW.STATUS AS \"status\",\n" +
            "       OW.CREATE_DATE AS \"createDate\",\n" +
            "       OW.UPDATE_TIME AS \"updateTime\",\n" +
            "       OW.CREATE_TIME AS \"createTime\",\n" +
            "       W.SPMS_ID AS \"spmsId\"\n" +
            "FROM ORDER_WORKSHOP OW\n" +
            "  LEFT JOIN ORDER_WORKSHOP_DTL OWD ON OW.UUID = OWD.UUID\n" +
            "  LEFT JOIN ORDER_WERKS_DTL WD ON OWD.SPMS_WS_ID = WD.SPMS_WS_ID AND OWD.ROW_ID = WD.ROW_ID_WS\n" +
            "  LEFT JOIN ORDER_WERKS W ON WD.UUID = W.UUID\n" +
            "WHERE OW.WERKS IN (\n" +
            "    SELECT SUW.WERKS FROM SPMS_USER SU LEFT JOIN SPMS_USER_WORKSHOP SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId}\n" +
            ") AND OW.WORKSHOP_CODE IN (\n" +
            "    SELECT SUW.WORKSHOP_CODE FROM SPMS_USER SU LEFT JOIN SPMS_USER_WORKSHOP SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId}\n" +
            "    ) \n" +
            "  AND OW.STATUS !='0' \n" +
            "<when test='orderStatus!=null'> AND OW.STATUS = #{orderStatus} </when> " +
            " <if test='applicationDateStart != null and applicationDateStart.trim() != &quot;&quot;'>" +
            "     AND OW.APPLICATION_DATE &gt;= #{applicationDateStart}" +
            " </if>" +
            " <if test='applicationDateEnd != null and applicationDateEnd.trim() != &quot;&quot;'>" +
            "     AND OW.APPLICATION_DATE &lt;= #{applicationDateEnd}" +
            " </if>" +
            " <if test='urgencyDegree != null and urgencyDegree.trim() != &quot;&quot;'>" +
            "     AND OW.URGENCY = #{urgencyDegree}" +
            " </if>" +
            " <if test='spmsWsId != null and spmsWsId.trim() != &quot;&quot;'>" +
            "     AND OW.SPMS_WS_ID = #{spmsWsId}" +
            " </if>" +
            " <if test='authorId != null and authorId.trim() != &quot;&quot;'>" +
            "     AND OW.WORK_CODE = #{authorId}" +
            " </if>" +
            " <if test='userName != null'>" +
            "     AND OW.LAST_NAME = #{userName}" +
            " </if>" +
            "    ORDER BY OW.CREATE_TIME DESC \n" +
            "</script>")
    List<Map<String, Object>> getOrderWS(@Param("userId") String userId,
                                         @Param("orderStatus") String orderStatus,
                                         @Param("applicationDateStart") String applicationDateStart,
                                         @Param("applicationDateEnd") String applicationDateEnd,
                                         @Param("urgencyDegree") String urgencyDegree,
                                         @Param("spmsWsId") String spmsWsId,
                                         @Param("authorId") String authorId,
                                         @Param("userName") String userName);

    @Select("<script>" +
            "SELECT DISTINCT  " +
            " OW.*," +
            " W.SPMS_ID " +
        //     " OW.UUID AS \"uuid\",\n" +
        //     "       OW.SPMS_WS_ID AS \"spmsWsId\",\n" +
        //     "       OW.URGENCY AS \"urgency\",\n" +
        //     "       OW.APPLICATION_DATE AS \"applicationDate\",\n" +
        //     "       OW.DATE_REQUIRED AS \"dateRequired\",\n" +
        //     "       OW.SUBJECT AS \"subject\",\n" +
        //     "       OW.WORK_CODE AS \"userId\",\n" +
        //     "       OW.LAST_NAME AS \"lastName\",\n" +
        //     "       OW.WERKS AS \"werks\",\n" +
        //     "       OW.WORKSHOP_CODE AS \"workshopCode\",\n" +
        //     "       OW.JUSTIFICATION AS \"justification\",\n" +
        //     "       OW.PROJECT_CODE AS \"projectCode\",\n" +
        //     "       OW.PROJECT_TYPE AS \"projectType\",\n" +
        //     "       OW.PROJECT_STATUS AS \"projectStatus\",\n" +
        //     "       OW.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
        //     "       OW.STATUS AS \"status\",\n" +
        //     "       OW.CREATE_DATE AS \"createDate\",\n" +
        //     "       OW.UPDATE_TIME AS \"updateTime\",\n" +
        //     "       OW.CREATE_TIME AS \"createTime\",\n" +
        //     "       W.SPMS_ID AS \"spmsId\"\n" +
            "FROM ORDER_WORKSHOP OW\n" +
            "  LEFT JOIN ORDER_WORKSHOP_DTL OWD ON OW.UUID = OWD.UUID\n" +
            "  LEFT JOIN ORDER_WERKS_DTL WD ON OWD.SPMS_WS_ID = WD.SPMS_WS_ID AND OWD.ROW_ID = WD.ROW_ID_WS\n" +
            "  LEFT JOIN ORDER_WERKS W ON WD.UUID = W.UUID\n" +
            "WHERE OW.WERKS IN (\n" +
            "    SELECT SUW.WERKS FROM SPMS_USER SU LEFT JOIN SPMS_USER_WORKSHOP SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{param.userId}\n" +
            ") AND OW.WORKSHOP_CODE IN (\n" +
            "    SELECT SUW.WORKSHOP_CODE FROM SPMS_USER SU LEFT JOIN SPMS_USER_WORKSHOP SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{param.userId}\n" +
            "    ) \n" +
            "<when test='param.orderStatus!=null'> AND OW.STATUS = #{param.orderStatus} </when> " +
            "    ORDER BY OW.CREATE_TIME DESC \n" +
            "</script>")
    @Results({
        @Result(column = "UPDATE_TIME",property = "updateTime",javaType = LocalDateTime.class,
        jdbcType = JdbcType.DATE,typeHandler = MyLocalDateTimeTypeHandler.class),
        @Result(column = "CREATE_TIME",property = "createTime",javaType = LocalDateTime.class,
        jdbcType = JdbcType.DATE,typeHandler = MyLocalDateTimeTypeHandler.class)
    })
    IPage<OrderWorkshopQueryResultVo> getOrderWS1(IPage<OrderWorkshopQueryResultVo> page, @Param("param") OrderWorkshopQueryVo orderWorkshopQueryVo);

    @Insert("INSERT INTO ORDER_WORKSHOP (" +
            "   UUID, " +
            "   URGENCY, " +
            "   APPLICATION_DATE, " +
            "   DATE_REQUIRED, " +
            "   SUBJECT, " +
            "   WORK_CODE, " +
            "   LAST_NAME, " +
            "   WERKS, " +
            "   WORKSHOP_CODE, " +
            "   JUSTIFICATION, " +
            "   PROJECT_CODE, " +
            "   PROJECT_TYPE, " +
            "   PROJECT_STATUS, " +
            "   STATUS, " +
            "   PURCHASE_LOCATION, " +
            "   CREATE_DATE) VALUES (\n" +
            "   #{ows.uuid}, \n" +
            "   #{ows.urgency}, \n" +
            "   #{ows.applicationDate}, \n" +
            "   #{ows.dateRequired}, \n" +
            "   #{ows.subject}, \n" +
            "   #{ows.userId}, \n" +
            "   #{ows.lastName}, \n" +
            "   #{ows.werks}, \n" +
            "   #{ows.workshopCode}, \n" +
            "   #{ows.justification}, \n" +
            "   #{ows.projectCode}, \n" +
            "   #{ows.projectType}, \n" +
            "   #{ows.projectStatus}, \n" +
            "   #{ows.status}, \n" +
            "   #{ows.purchaseLocation}, \n" +
            "   #{ows.createDate}\n" +
            ")")
    int createOrderWs(@Param("ows") OrderWorkshop ows);


    @Select("SELECT OW.UUID AS \"uuid\",\n" +
            "       OW.SPMS_WS_ID AS \"spmsWsId\",\n" +
            "       OW.URGENCY AS \"urgency\",\n" +
            "       OW.APPLICATION_DATE AS \"applicationDate\",\n" +
            "       OW.DATE_REQUIRED AS \"dateRequired\",\n" +
            "       OW.SUBJECT AS \"subject\",\n" +
            "       OW.WORK_CODE AS \"userId\",\n" +
            "       OW.LAST_NAME AS \"lastName\",\n" +
            "       OW.WERKS AS \"werks\",\n" +
            "       OW.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OW.JUSTIFICATION AS \"justification\",\n" +
            "       OW.PROJECT_CODE AS \"projectCode\",\n" +
            "       OW.PROJECT_TYPE AS \"projectType\",\n" +
            "       OW.PROJECT_STATUS AS \"projectStatus\",\n" +
            "       OW.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "       OW.STATUS AS \"status\",\n" +
            "       OW.CREATE_DATE AS \"createDate\",\n" +
            "       OW.UPDATE_TIME AS \"updateTime\",\n" +
            "       OW.CREATE_TIME AS \"createTime\"\n" +
            "FROM ORDER_WORKSHOP OW WHERE OW.WERKS IN (\n" +
            "    SELECT SUW.WERKS FROM SPMS_USER SU LEFT JOIN SPMS_USER_WORKSHOP SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId}\n" +
            ") AND OW.STATUS = #{status} ORDER BY OW.SPMS_WS_ID, OW.WORKSHOP_CODE ASC\n")
    List<Map<String, Object>> getOrderWSList(@Param("userId") String userId, @Param("status") String status);

    @Select("SELECT OL.WORK_CODE AS \"userId\",\n" +
            "       U.LAST_NAME AS \"lastName\",\n" +
            "       OL.COMMENTS AS \"comments\",\n" +
            "       OL.CREATE_TIME AS \"createTime\",\n" +
            "       OL.UUID AS \"uuid\",\n" +
            "       OL.LOG_AUTO_ID AS \"logAutoId\"\n" +
            "FROM ORDER_LOG OL\n" +
            "    LEFT JOIN SPMS_USER U ON OL.WORK_CODE = U.WORK_CODE\n" +
            "WHERE OL.UUID IN (\n" +
            "    SELECT OWD.UUID FROM ORDER_WERKS_DTL OWD, ORDER_WERKS OW, ORDER_WORKSHOP W\n" +
            "    WHERE OWD.UUID = OW.UUID AND OWD.SPMS_WS_ID = W.SPMS_WS_ID AND W.UUID = #{uuid}\n" +
            "    ) ORDER BY OL.LOG_AUTO_ID DESC")
    List<Map<String, Object>> getOrderLogWByUuid(@Param("uuid") String uuid);

    @Select("SELECT DISTINCT OW.OA_BIANHAO AS \"oaBianhao\" FROM ORDER_WERKS OW\n" +
            "    LEFT JOIN ORDER_WERKS_DTL OWD ON OW.UUID = OWD.UUID\n" +
            "    LEFT JOIN ORDER_WORKSHOP_DTL WD ON OWD.SPMS_WS_ID = WD.SPMS_WS_ID AND OWD.ROW_ID_WS = WD.ROW_ID\n" +
            "    LEFT JOIN ORDER_WORKSHOP W ON WD.UUID = W.UUID\n" +
            "WHERE W.UUID = #{uuid}\n")
    String getOrderLogOaByUuid(@Param("uuid")String uuid);
}
